USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[RekidaiKirokuInfo] スクリプト日付: 2024/10/28 10:00:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RekidaiKirokuInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RekidaiKirokuInfo]
GO

CREATE PROCEDURE [dbo].[RekidaiKirokuInfo]
	@TAIKAINO int,
	@KYOGINO int,
	@ID int
AS 
BEGIN

	DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '

			SELECT REK.SENSYUMEI
				 , REK.SYOZOKUMEI
				 , REK.KIROKU
				 , REK.HIDUKE
				 , REK.SABUNO
				 , LAP.ID
				 , COM.COMMENTRYAKU

			FROM TBL_REKIDAI AS REK

			LEFT JOIN TBL_KYOGI AS KYO
			ON REK.TAIKAINO = KYO.TAIKAINO
			AND REK.KYOGINO = KYO.KYOGINO

			LEFT JOIN TBL_LAP AS LAP
			ON REK.SABUNO = LAP.LAPNO
			AND KYO.SYUMOKUNO = LAP.SYUMOKUNO

			LEFT JOIN TBL_COMMENT AS COM
			ON REK.COMMENTNO = COM.COMMENTNO

			WHERE REK.TAIKAINO = '+STR(@TAIKAINO) + '
			AND REK.KYOGINO = '+STR(@KYOGINO)

	IF @ID != -1
	  BEGIN
	    SET @SQL = @SQL + ' AND LAP.ID = '+STR(@ID)+''	
      END

    SET @SQL = @SQL + 'ORDER BY REK.SABUNO, REK.KIROKU'

	EXECUTE(@SQL)
END